﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using XinYiOffice.Common;
using System.Web.UI.WebControls;
using System.Web;

namespace XinYiOffice.Basic
{
    public class ProjectInfoServer
    {
        /// <summary>
        /// 根据用户id获取我相关的项目
        /// </summary>
        /// <param name="accountId"></param>
        /// <param name="topNum"></param>
        /// <returns></returns>
        public static DataTable GetMyProjectList(int accountId,int topNum,int TenantId)
        {
            string sql = "select * from ProjectInfo where Id in({0}) and TenantId={1} and State<>4 order by Id desc";
            string mysql = string.Format("select {0} Id from vProjectInfoByAccount where AccountId={1} or OrganiserAccountId={1} or ExecutorAccountId={1} or ProjectManagerAccountId={1} or ByClientInfoId={1} or SurveyorAccountId={1}", string.Empty, accountId);
            if (topNum!=0)
            {
                mysql = string.Format("select top {0} Id from vProjectInfoByAccount where AccountId={1} or OrganiserAccountId={1} or ExecutorAccountId={1} or ProjectManagerAccountId={1} or ByClientInfoId={1} or SurveyorAccountId={1}", topNum, accountId);
            }
            return Common.DbHelperSQL.Query(string.Format(sql, mysql, TenantId)).Tables[0];
        }


        /// <summary>
        /// 全部项目
        /// </summary>
        /// <param name="accountId"></param>
        /// <param name="topNum"></param>
        /// <returns></returns>
        public static DataTable GetProjectList(string strWhere, int TenantId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM vProjectInfoList ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
                strSql.Append(string.Format(" and TenantId={0} ", TenantId));
            }
            strSql.Append(" order by CreateTime desc");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        public static string GetProjectListSql(string strWhere, int TenantId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM vProjectInfoList ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
                strSql.Append(string.Format(" and TenantId={0} ", TenantId));
            }
            strSql.Append(" order by Id desc");
            return strSql.ToString();
        }

        /// <summary>
        ///  议题跟踪
        /// </summary>
        /// <param name="accountId"></param>
        /// <param name="topNum"></param>
        /// <returns></returns>
        public static DataTable GetIssueTrackerList(string strWhere, int TenantId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM vIssueTracker ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
                strSql.Append(string.Format(" and TenantId={0} ",TenantId));
            }
            strSql.Append(" order by CreateTime desc");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        public static string GetIssueTrackerListSql(string strWhere, int TenantId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM vIssueTracker ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
                strSql.Append(string.Format(" and TenantId={0} ", TenantId));
            }
            strSql.Append(" order by CreateTime desc");
            return strSql.ToString();
        }

        /// <summary>
        ///  议题跟踪历史处理 列表
        /// </summary>
        /// <param name="accountId"></param>
        /// <param name="topNum"></param>
        /// <returns></returns>
        public static DataTable GetIssueTrackerSubsequentList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM vIssueTrackerSubsequent ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by CreateTime desc");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获取项目成员
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable GetProjectMembers(string strWhere, int TenantId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM vProjectMembers ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
                strSql.Append(string.Format(" and TenantId={0} ", TenantId));
            }
            strSql.Append(" order by CreateTime desc");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获取项目类型
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable GetProjectTypes(string strWhere, int TenantId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM vProjectTypes ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
                strSql.Append(string.Format(" and TenantId={0} ", TenantId));
            }
            strSql.Append(" order by CreateTime desc");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获取项目角色
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable GetProjectRoles(string strWhere, int TenantId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM vProjectRoles ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
                strSql.Append(string.Format(" and TenantId={0} ", TenantId));
            }
            strSql.Append(" order by CreateTime desc");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

    }
}
